package by.euanpa.android.grodnobusstop.content.db;

import java.util.Random;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import by.euanpa.android.grodnobusstop.content.provider.contract.Contract;

public class DbHelper extends SQLiteOpenHelper {

	private static final String NAME = "grodno.bus.stop.db";
	private static final int VERSION = 5;

	public static final String[] BUS_STOP_COLUMNS = { 
		Contract.BusStopColumns.ID, 
		Contract.BusStopColumns.NAME,
		Contract.BusStopColumns.LONGITUDE,
		Contract.BusStopColumns.LATITUDE };

	public static final int ID_COL_INDEX = 0;
	public static final int NAME_COL_INDEX = 1;
	public static final int LONGITUDE_COL_INDEX = 2;
	public static final int LATITUDE_COL_INDEX = 3;

	private static final String CREATE_BUS_STOP_TABLE_SQL = "CREATE TABLE " + 
			Contract.BusStopColumns.TABLE_NAME + " ("
			+ BUS_STOP_COLUMNS[ID_COL_INDEX] + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ BUS_STOP_COLUMNS[NAME_COL_INDEX] + " VARCHAR NOT NULL , "
			+ BUS_STOP_COLUMNS[LONGITUDE_COL_INDEX] + " VARCHAR NOT NULL, "
			+ BUS_STOP_COLUMNS[LATITUDE_COL_INDEX] + " VARCHAR NOT NULL)";

	private static final String DROP_BUS_STOP_TABLE_SQL = "DROP TABLE IF EXISTS " + Contract.BusStopColumns.TABLE_NAME;

	
	public static final String[] ROUTE_COLUMNS = { 
		Contract.RouteColumns.ID, 
		Contract.RouteColumns.NAME,
		Contract.RouteColumns.NUMBER};

	public static final int NUMBER_COL_INDEX = 2;

	private static final String CREATE_ROUTE_TABLE_SQL = "CREATE TABLE " + 
			Contract.RouteColumns.TABLE_NAME + " ("
			+ ROUTE_COLUMNS[ID_COL_INDEX] + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ ROUTE_COLUMNS[NAME_COL_INDEX] + " VARCHAR NOT NULL, "
			+ ROUTE_COLUMNS[NUMBER_COL_INDEX] + " VARCHAR NOT NULL)";

	private static final String DROP_ROUTE_TABLE_SQL = "DROP TABLE IF EXISTS " + Contract.RouteColumns.TABLE_NAME;

	public static final String[] RELATION_COLUMNS = { 
		Contract.RelationColumns.ID, 
		Contract.RelationColumns.BUS_STOP_ID,
		Contract.RelationColumns.ROUTE_ID, 
		Contract.RelationColumns.TIME_TABLE_MONDAY,
		Contract.RelationColumns.TIME_TABLE_SATURDAY,
		Contract.RelationColumns.TIME_TABLE_WORKDAY};

	public static final int BUS_STOP_ID_COL_INDEX = 1;
	public static final int ROUTE_ID_COL_INDEX = 2;
	public static final int TIME_TABLE_MONDAY_COL_INDEX = 3;
	public static final int TIME_TABLE_SATURDAY_COL_INDEX = 4;
	public static final int TIME_TABLE_WORKDAY_COL_INDEX = 5;

	private static final String CREATE_RELATION_TABLE_SQL = "CREATE TABLE " + 
			Contract.RelationColumns.TABLE_NAME + " ("
			+ RELATION_COLUMNS[ID_COL_INDEX] + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ RELATION_COLUMNS[BUS_STOP_ID_COL_INDEX] + " VARCHAR NOT NULL, "
			+ RELATION_COLUMNS[ROUTE_ID_COL_INDEX] + " VARCHAR NOT NULL, "
			+ RELATION_COLUMNS[TIME_TABLE_MONDAY_COL_INDEX] + " VARCHAR, "
			+ RELATION_COLUMNS[TIME_TABLE_SATURDAY_COL_INDEX] + " VARCHAR, "
			+ RELATION_COLUMNS[TIME_TABLE_WORKDAY_COL_INDEX] + " VARCHAR)";

	private static final String DROP_RELATION_TABLE_SQL = "DROP TABLE IF EXISTS " + Contract.RelationColumns.TABLE_NAME;
	
	private static final ContentValues[] BUS_STOP_INIT_VALUES = new ContentValues[100];
	private static final ContentValues[] ROUTE_INIT_VALUES = new ContentValues[100];
	private static final ContentValues[] RELATION_INIT_VALUES = new ContentValues[1000];
	
	
	static {
		for (int i = 0; i < 100; i++) {
			BUS_STOP_INIT_VALUES[i] = new ContentValues();
			BUS_STOP_INIT_VALUES[i].put(Contract.BusStopColumns.ID, i);
			BUS_STOP_INIT_VALUES[i].put(Contract.BusStopColumns.NAME, "BUS STOP " + i);
			BUS_STOP_INIT_VALUES[i].put(Contract.BusStopColumns.LONGITUDE,  i);
			BUS_STOP_INIT_VALUES[i].put(Contract.BusStopColumns.LATITUDE,  i);
			
			ROUTE_INIT_VALUES[i] = new ContentValues();
			ROUTE_INIT_VALUES[i].put(Contract.RouteColumns.ID, i);
			ROUTE_INIT_VALUES[i].put(Contract.RouteColumns.NAME, "Route " + i);
			ROUTE_INIT_VALUES[i].put(Contract.RouteColumns.NUMBER, i * 100);
		}
		Random r = new Random();
		for (int i = 0; i < 1000; i++) {
			int bus = r.nextInt(100);
			int route = r.nextInt(100);
			RELATION_INIT_VALUES[i] = new ContentValues();
			RELATION_INIT_VALUES[i].put(Contract.RelationColumns.ID, i);
			RELATION_INIT_VALUES[i].put(Contract.RelationColumns.BUS_STOP_ID, bus);
			RELATION_INIT_VALUES[i].put(Contract.RelationColumns.ROUTE_ID, route);
			Log.d("neron", bus + " - " + route);
		}
	}

	public DbHelper(Context context) {
		super(context, NAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.beginTransaction();
			db.execSQL(CREATE_BUS_STOP_TABLE_SQL);
			db.execSQL(CREATE_ROUTE_TABLE_SQL);
			db.execSQL(CREATE_RELATION_TABLE_SQL);
			fill(db);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	private void fill(SQLiteDatabase db) {
		for (int i = 0; i < BUS_STOP_INIT_VALUES.length; i++) {
			db.insert(Contract.BusStopColumns.TABLE_NAME, null, BUS_STOP_INIT_VALUES[i]);
			db.insert(Contract.RouteColumns.TABLE_NAME, null, ROUTE_INIT_VALUES[i]);
		}
		for (int i = 0; i < 1000; i++) {
			db.insert(Contract.RelationColumns.TABLE_NAME, null, RELATION_INIT_VALUES[i]);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			db.beginTransaction();
			db.execSQL(DROP_BUS_STOP_TABLE_SQL);
			db.execSQL(DROP_ROUTE_TABLE_SQL);
			db.execSQL(DROP_RELATION_TABLE_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		onCreate(db);
	}
}
